/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package delooratomssqlmigra;

/**
 *
 * @author Prk
 */
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Date;

public class DeloOraToMsSqlMigra {

    /**
     *
     */
    public static Hashtable<String,String> params = new Hashtable<>();
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Date start_time = new Date();
        if ((args.length!=6) && (args.length!=0))
        {
            System.out.println("Неверное использование параметров:");
            System.out.println("-o 192.168.73.24:1521:orcl");
            System.out.println("-ou delo");
            System.out.println("-op admin");
            System.out.println("-m PRKNT\\SQLEXPRESS;databaseName=DELO_TMP");
            System.out.println("-mu sa");
            System.out.println("-mp Sa1");
            return;
        }
        params.clear();
        if (args.length>0){
        for(int i=0; i<3; i++) {
            switch(args[2*i].toUpperCase()) {
                case "-O":
                    params.put("O", args[2*i+1]);
                    break;
                case "-OU":
                    params.put("OU", args[2*i+1]);
                    break;
                case "-OP":
                    params.put("OP", args[2*i+1]);
                    break;
                case "-M":
                    params.put("M", args[2*i+1]);
                    break;
                case "-MU":
                    params.put("MU", args[2*i+1]);
                    break;
                case "-MP":
                    params.put("MP", args[2*i+1]);
                    break;
                default:
                    System.out.println("Неверное использование параметров:");
                    System.out.println("-o 192.168.73.24:1521:orcl");
                    System.out.println("-ou delo");
                    System.out.println("-op admin");
                    System.out.println("-m PRKNT\\SQLEXPRESS;databaseName=DELOTMP_DB");
                    System.out.println("-mu sa");
                    System.out.println("-mp Sa1");
                    return;
            }            
        }
        }
        if (params.isEmpty()) {
            params.put("O", "192.168.73.24:1521:orcl");
            params.put("OU", "delo");
            params.put("OP", "admin");
            params.put("M", "PRKNT\\SQLEXPRESS;databaseName=DELOTMP_DB");
            params.put("MU", "sa");
            params.put("MP", "Sa1");
        }
        ConnectOracle();
        ConnectMSSQL();
        CreateAllTable();
        runThreads();
        System.out.println("Время начала: " + start_time.toString()+"\n");
        System.out.println("Время исполнения: " + (new Date()).toString());
    }
    
    private static ArrayList<Thread> ListThread = new ArrayList<>();
    private static ArrayList<InsThread> InsThreads = new ArrayList<>();
    
    private static void appendThread(String TableName) throws SQLException {
        InsThread obj1 = new InsThread(cnnmssql, cnnoracle, TableName);
        InsThreads.add(obj1);
        Thread th1 = new Thread(obj1,TableName);
        ListThread.add(th1);
    }
    
    private static InsThread getInsThread(String Name) {
        for (int i=0;i<InsThreads.size();i++) {
            if(InsThreads.get(i).getTableName().equals(Name))
            {
                return InsThreads.get(i); 
            }
        }
        return null;
    }
    
    // Количество одновременно запущеных потоков
    public static int CountThread = 2;
    private ArrayList<String> Error = new ArrayList<>();
    
    private static void runThreads() {
        ArrayList<Thread> ActiveThread = new ArrayList<>();
        for (int i=0; i<CountThread;i++) {
            ActiveThread.add(ListThread.get(0));
            ListThread.get(0).start();
            ListThread.remove(0);
        }
        InsThread obj1;
        while (ActiveThread.size() > 0) {
            for (int i=0; i<ActiveThread.size();i++) {  
                if (! ActiveThread.get(i).isAlive()) {
                    System.out.printf("Завершен процесс %1$s \n", 
                            ActiveThread.get(i).getName());
                    obj1 = getInsThread(ActiveThread.get(i).getName());
                    for (int j=0; j<obj1.getError().size(); j++) {
                        System.out.print(obj1.getError().get(j)+"\n");
                    }
                    ActiveThread.remove(i);
                    if (ListThread.size()>0) {
                        ActiveThread.add(ListThread.get(0));
                        ListThread.get(0).start();
                        ListThread.remove(0);
                        break;
                    }
                }
            }      
        }
    }
    
    private static Connection cnnoracle;
    private static Connection cnnmssql;
    
    private static void ConnectMSSQL() {
       try
       {
           DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
           
           cnnmssql = DriverManager.getConnection(
                "jdbc:sqlserver://"+params.get("M"), 
                params.get("MU"), 
                params.get("MP"));
           //stmtmssql = connection.createStatement();
//           ResultSet rs = stmt.executeQuery("select SYS_VERSION from DELO_OWNER");
//           if (rs.next()) {
//               System.out.printf("Версия базы: %1$s \n" , rs.getString(1));
//           }
//           rs.close();
       }
       catch (SQLException ex ) {
           System.out.printf("Ошибка %1$s \n",ex.getMessage());
       }
    }
    
           
    private static void ConnectOracle() {
       try
       {
           DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
           cnnoracle = DriverManager.getConnection(
                "jdbc:oracle:thin:@"+params.get("O"), 
                params.get("OU"), 
                params.get("OP"));
           cnnoracle.setAutoCommit(false);
        }
       catch (SQLException ex ) {
           System.out.printf("Ошибка %1$s \n",ex.getMessage());
       }
    }
    
    private static void CreateAllTable() {
       SimpleDateFormat df = new java.text.SimpleDateFormat ("dd-MM-yyyy HH:mm:ss");
       try
       {
           Statement stmtoracle = cnnoracle.createStatement();
           ResultSet rst; 
           rst= stmtoracle.executeQuery("select COUNT(TABLE_NAME) from user_tables");
           Integer counttable = 0;
           if (rst.next()) {
                counttable = rst.getInt(1);
           }
           rst.close();
           rst = stmtoracle.executeQuery("select TABLE_NAME from user_tables");
           String tablename;
           String createtable;
           Statement stmtct;
           Statement stmtoracle2;
           Integer r=0;
           while (rst.next()) {
               r++;
               tablename=rst.getString(1);
//               if (!"RC_INP".equals(tablename.toUpperCase())) {
//                   continue;
//               }
               appendThread(tablename);
               System.out.printf("--ТАБЛИЦА: %1$s \n" , tablename);
               createtable = "CREATE TABLE " + tablename + " ( ";
               stmtoracle2 = cnnoracle.createStatement();
               ResultSet rsf = stmtoracle2.executeQuery("select * from " + tablename);
               for (int i=1; i<=rsf.getMetaData().getColumnCount(); i++) {
                    System.out.printf("--     ПОЛЕ: %1$s" , rsf.getMetaData().getColumnName(i));
                    System.out.printf("; ТИП: %1$s(%2$s) - %3$s\n" , 
                            rsf.getMetaData().getColumnTypeName(i),
                            rsf.getMetaData().getColumnDisplaySize(i),
                            String.valueOf(rsf.getMetaData().getColumnType(i)));
                    createtable+=rsf.getMetaData().getColumnName(i);
                    switch (rsf.getMetaData().getColumnType(i)) {
                        case 2: {
                            createtable+=" INT NULL";
                            break;
                        }
                        case 12: {
                            createtable+=" VARCHAR("+rsf.getMetaData().getColumnDisplaySize(i)+") NULL";
                            break;
                        }
                        case 93: {
                            createtable+=" DATETIME2 NULL";
                            break;
                        }
                        case 1: {
                            createtable+=" CHAR("+rsf.getMetaData().getColumnDisplaySize(i)+") NULL";
                            break;
                        }
                        case -4: {
                            createtable+=" VARBINARY(MAX) NULL";
                            break;
                        }
                        case -3: {
                            createtable+=" VARBINARY("+rsf.getMetaData().getColumnDisplaySize(i)+") NULL";
                            break;
                        }
                        case -1: {
                            createtable+=" VARCHAR(MAX) NULL";
                            break;
                        }
                    }
                    if (i<rsf.getMetaData().getColumnCount()) {
                        createtable+=",";
                    } else {
                        createtable+=");";
                    }
               }
               System.out.print("----------------- \n");
               System.out.printf("%1$s \n" , createtable);
               stmtct = cnnmssql.createStatement();
               try {
                    if (stmtct.execute("DROP TABLE " + tablename + ";")) {
                         System.out.print("--Удаление таблицы - Ошибка \n");
                    } else {
                         System.out.printf("--Удаление таблицы -  %1$s/%2$s Завершенно \n",r.toString(),counttable.toString());
                    }
               } catch(Exception e) {
                    System.out.print("--Удаление таблицы - Ошибка \n" + e.getMessage() + "\n");
               }
               try {
                    if (stmtct.execute(createtable)) {
                         System.out.print("--Создание таблицы - Ошибка \n");
                    } else {
                        System.out.printf("--Создание таблицы - %1$s/%2$s Завершенно \n",r.toString(),counttable.toString());
                    }
               } catch(Exception e) {
                    System.out.print("--Создание таблицы - Ошибка \n" + e.getMessage() + "\n");
               }
               System.out.print("----------------- \n");
               stmtct.close();
               
               rsf.close();
               stmtoracle2.close();
           }
           rst.close();
       }
       catch (SQLException ex ) {
           System.out.printf("Ошибка %1$s \n",ex.getMessage());
       }
        
    }
    
    
}
